﻿
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;


using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Reflection;


namespace Star.Tools.Excel
{
    /// <summary>
    /// 导入到DataTable
    /// </summary>
    public  class ImportDataTable
    {
   
        #region 由Excel导入DataTable 

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <param name="iSheetIndex"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string strFilePath, int iSheetIndex, string strTableName)
        {
            if (string.IsNullOrEmpty(strFilePath))
            {
                return null;
            }
            DataTable dt = new DataTable();
            IWorkbook workbook;
            using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
            {
                workbook = WorkbookFactory.Create(file);
            }
            ISheet sheet = workbook.GetSheetAt(iSheetIndex);
            dt = ExcelToDataTable(sheet, 0, true);
            return dt;
        }
        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径,可传空值</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ExcelToDataTable(string excelFilePath, string sheetName, int headerRowIndex)
        {
            
            if (string.IsNullOrEmpty(excelFilePath))
            {
                return null;
            }

            using (FileStream file = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
            {
                bool isCompatible = NpoiHelper.GetIsCompatible(excelFilePath);
                return ExcelToDataTable(file, sheetName, headerRowIndex, isCompatible);
            }
        }
        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataTable</returns>
        public static DataTable ExcelToDataTable(Stream excelStream, string sheetName, int headerRowIndex, bool isCompatible)
        {
            IWorkbook workbook = CreateWorkbook(isCompatible, excelStream); //WorkbookFactory.Create(excelStream);
            ISheet sheet = null;
            int sheetIndex = -1;
            if (int.TryParse(sheetName, out sheetIndex))
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            else
            {
                sheet = workbook.GetSheet(sheetName);
            }
            DataTable table = GetDataTableSheet(sheet, headerRowIndex);
            excelStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 读取Excel流到DataTable
        /// </summary>
        /// <param name="stream">Excel流</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns>指定sheet中的数据</returns>
        public static DataTable ExcelToDataTable(Stream stream, int iSheetIndex, int HeaderRowIndex)
        {
            try
            {
                DataTable dt = new DataTable();
                IWorkbook workbook;
                using (stream)
                {
                    workbook = WorkbookFactory.Create(stream);
                }
                ISheet sheet = workbook.GetSheetAt(iSheetIndex);
                dt = ExcelToDataTable(sheet, 0, true);
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }
        #endregion


        #region 由Excel导入DataSet，如果有多个工作表 则导入多个DataTable

        /// <summary>
        /// 通过IWorkbook对象获取DataSet
        /// </summary>
        /// <param name="workbook">workbook</param>
        /// <param name="haveColumnName">workbook对象中首行是否为列名</param>
        /// <returns>DataSet</returns>
        private static DataSet GetDataSet(IWorkbook workbook, bool haveColumnName)
        {
            if (workbook == null || workbook.NumberOfSheets == 0) { return null; }
            DataSet dataset = new DataSet();
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                DataTable dt = GetDataTable(workbook, haveColumnName, i);
                if (dt != null)
                {
                    dataset.Tables.Add(dt);
                }
            }
            return dataset;
        }
        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。可传空值</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataSet</returns>
        public static DataSet ExcelToDataSet(string excelFilePath, int headerRowIndex)
        {
            if (string.IsNullOrEmpty(excelFilePath))
            {
                return null;
            }
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                bool isCompatible = NpoiHelper.GetIsCompatible(excelFilePath);
                return ExcelToDataSet(stream, headerRowIndex, isCompatible);
            }
        }

        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <param name="isCompatible">是否为兼容模式</param>
        /// <returns>DataSet</returns>
        public static DataSet ExcelToDataSet(Stream excelStream, int headerRowIndex, bool isCompatible)
        {
            DataSet ds = new DataSet();
            IWorkbook workbook = CreateWorkbook(isCompatible, excelStream);
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                ISheet sheet = workbook.GetSheetAt(i);
                DataTable table = GetDataTableSheet(sheet, headerRowIndex);
                ds.Tables.Add(table);
            }
            excelStream.Close();
            workbook = null;
            return ds;
        }


        #endregion
        /// <summary>
        /// 创建工作薄(依据文件流)
        /// </summary>
        /// <param name="isCompatible"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
        {
            if (isCompatible)
            {
                return new HSSFWorkbook(stream);
            }
            else
            {
                return new XSSFWorkbook(stream);
            }
        }
        
        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(ISheet sheet, int HeaderRowIndex, bool needHeader)
        {
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            try
            {
                if (HeaderRowIndex < 0 || !needHeader)
                {
                    headerRow = sheet.GetRow(0);
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        // var cell = headerRow.GetCell(i);
                        DataColumn column = new DataColumn(Convert.ToString(i));
                        table.Columns.Add(column);


                    }

                }
                else
                {
                    headerRow = sheet.GetRow(HeaderRowIndex);
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        if (headerRow.GetCell(i) == null)
                        {
                            if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                            {
                                DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                                table.Columns.Add(column);
                            }
                            else
                            {
                                DataColumn column = new DataColumn(Convert.ToString(i));
                                table.Columns.Add(column);
                            }

                        }
                        else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                            table.Columns.Add(column);
                        }
                    }
                }
                int rowCount = sheet.LastRowNum;
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    try
                    {
                        IRow row;
                        if (sheet.GetRow(i) == null)
                        {
                            row = sheet.CreateRow(i);
                        }
                        else
                        {
                            row = sheet.GetRow(i);
                        }

                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j <= cellCount; j++)
                        {
                            try
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j));
                                    //switch (row.GetCell(j).CellType)
                                    //{
                                    //    case CellType.String:
                                    //        string str = row.GetCell(j).StringCellValue;
                                    //        if (str != null && str.Length > 0)
                                    //        {
                                    //            dataRow[j] = str.ToString();
                                    //        }
                                    //        else
                                    //        {
                                    //            dataRow[j] = null;
                                    //        }
                                    //        break;
                                    //    case CellType.Numeric:
                                    //        if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                                    //        {
                                    //            dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                    //        }
                                    //        else
                                    //        {
                                    //            dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                    //        }
                                    //        break;
                                    //    case CellType.Boolean:
                                    //        dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                    //        break;
                                    //    //case CellType.Error:
                                    //    //    dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                    //    //    break;
                                    //    case CellType.Formula:
                                    //        switch (row.GetCell(j).CachedFormulaResultType)
                                    //        {
                                    //            case CellType.String:
                                    //                string strFORMULA = row.GetCell(j).StringCellValue;
                                    //                if (strFORMULA != null && strFORMULA.Length > 0)
                                    //                {
                                    //                    dataRow[j] = strFORMULA.ToString();
                                    //                }
                                    //                else
                                    //                {
                                    //                    dataRow[j] = null;
                                    //                }
                                    //                break;
                                    //            case CellType.Numeric:
                                    //                dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                    //                break;
                                    //            case CellType.Boolean:
                                    //                dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                    //                break;
                                    //            //case CellType.Error:
                                    //            //    dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                    //            //    break;
                                    //            default:
                                    //                dataRow[j] = "";
                                    //                break;
                                    //        }
                                    //        break;
                                    //    default:
                                    //        dataRow[j] = "";
                                    //        break;
                                    //}
                                }
                            }
                            catch (Exception ex)
                            {
                                throw;
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    catch (Exception)
                    {

                    }
                }
            }
            catch (Exception)
            {

            }
            return table;
        }

        /// <summary>
        /// 从工作表中生成DataTable
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="headerRowIndex"></param>
        /// <returns></returns>
        public static DataTable GetDataTableSheet(ISheet sheet, int headerRowIndex)
        {
            DataTable table = new DataTable();

            if (sheet.LastRowNum <= 0) return table;

            IRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                {
                    // 如果遇到第一个空列，则不再继续向后读取
                    cellCount = i;
                    break;
                }
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                //如果遇到某行的第一个单元格的值为空，则不再继续向下读取
                if (row != null && row.GetCell(0) != null && !string.IsNullOrEmpty(row.GetCell(0).ToString()))
                {
                    DataRow dataRow = table.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        dataRow[j] = cell == null ? "" : cell.ToString();
                    }

                    table.Rows.Add(dataRow);
                }
            }

            return table;
        }


        /// <summary>
        /// 通过IWorkbook对象获取DataTable
        /// </summary>
        /// <param name="workbook">workbook</param>
        /// <param name="haveColumnName">workbook对象中首行是否为列名</param>
        /// <param name="sheetIndex">workbook中的sheet索引</param>
        /// <returns>DataTable</returns>
        private static DataTable GetDataTable(IWorkbook workbook, bool haveColumnName, int sheetIndex = 0)
        {
            if (workbook == null || workbook.NumberOfSheets == 0) { return null; }
            ISheet sheet = workbook.GetSheetAt(sheetIndex);
            if (sheet == null) { return null; }

            DataTable dt = new DataTable();

            int startRow = 0;
            IRow fristRow = sheet.GetRow(0);//第一行
            int cellCount = fristRow.LastCellNum;//一行的最后一个cell编号，即总列数

            if (haveColumnName)//第一行是Table的列名
            {
                for (int i = fristRow.FirstCellNum; i < cellCount; i++)//循环每列，从第一列开始
                {
                    ICell cell = fristRow.GetCell(i);//当前列
                    if (cell != null)
                    {
                        string cellValue = cell.StringCellValue;//列名
                        if (cellValue != null)
                        {
                            DataColumn dc = new DataColumn(cellValue);
                            dt.Columns.Add(dc);
                        }
                    }
                }
                startRow = sheet.FirstRowNum + 1;//从第二行开始，即除开第一行列名
            }
            else
            {
                for (int i = fristRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn dc = new DataColumn();
                    dt.Columns.Add(dc);
                }
                startRow = sheet.FirstRowNum;
            }
            for (int i = startRow; i < sheet.LastRowNum; i++)//不能小于rowCount,否则最后一条数据不能读取
            {
                IRow row = sheet.GetRow(i);
                if (row == null)//没有数据的行默认是null
                { continue; }
                DataRow dr = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)//没有数据的单元格默认是null
                    {
                        dr[j] = row.GetCell(j).ToString();//当前单元格
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;

        }


        /// <summary>获取单元格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static object GetCellValue(ICell cell)
        {
            switch (cell.CellType)
            {
                case CellType.String:
                    string str = cell.StringCellValue;
                    if (str != null && str.Length > 0)
                    {
                        return str.ToString();
                    }
                    else
                    {
                        return null;
                    }
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return DateTime.FromOADate(cell.NumericCellValue).ToString("yyyy-MM-dd HH:mm:ss.fff");
                    }
                    else
                    {
                        return Convert.ToDouble(cell.NumericCellValue);
                    }
                case CellType.Boolean:
                    return Convert.ToString(cell.BooleanCellValue);
                //case CellType.Error:
                //    return ErrorEval.GetText(cell.ErrorCellValue);
                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.String:
                            string strFORMULA = cell.StringCellValue;
                            if (strFORMULA != null && strFORMULA.Length > 0)
                            {
                                return strFORMULA.ToString();
                            }
                            else
                            {
                                return null;
                            }
                        case CellType.Numeric:
                            return Convert.ToString(cell.NumericCellValue);
                        case CellType.Boolean:
                            return Convert.ToString(cell.BooleanCellValue);
                        //case CellType.Error:
                        //    return ErrorEval.GetText(cell.ErrorCellValue);
                        default:
                            return "";
                    }
                default:
                    return "";
            }
        }
       
    }
}
